package br.com.datasun.biblioteca.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

import br.com.datasun.biblioteca.dao.factory.ConnectionFactory;
import br.com.datasun.biblioteca.entities.Assunto;
import br.com.datasun.biblioteca.entities.Autor;
import br.com.datasun.biblioteca.entities.Categoria;
import br.com.datasun.biblioteca.exceptions.DAOException;

public class AutorDAO {

	public List<Autor> listarAutoresDisponiveis(Long idAutor) throws DAOException{
		List<Autor> resultado = new ArrayList<Autor>();
		Connection connection = null;
		PreparedStatement preparedStatement   = null;
		ResultSet resultSet   = null;
		StringBuilder sql = new StringBuilder();
		sql.append("  select a.*                                                 ");
		sql.append("    from autor a											 ");	
	    sql.append("    where not exists (select 1                               ");
	    sql.append("                           from livro_autor l 				 ");
	    sql.append("                          where l.id_autor = a.id            ");
	    sql.append("                            and l.id_livro = ? )             ");

		
		try{
			connection = ConnectionFactory.createConnectionToMySQL();
			preparedStatement = connection.prepareStatement(sql.toString());
			preparedStatement.setLong(1,idAutor);
			resultSet = preparedStatement.executeQuery();
			while (resultSet.next()) {
				long id             = resultSet.getLong(1);
				String nome         = resultSet.getString(2);
				Autor autor = new Autor();
				autor.setId(id);
				autor.setNome(nome);
				
				resultado.add(autor);
			}
		}catch (Exception e) {
			throw new DAOException(e);
		}finally{
			try{
				if(resultSet != null){
					resultSet.close();
				}
				if(preparedStatement != null){
					preparedStatement.close();
				}
				if(connection != null){
					connection.close();
				}
			}catch (Exception e) {
				throw new DAOException(e);
			}
		}
		return resultado;
	}
}
